﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Drawing.Drawing2D;
//using PopupControl;

namespace CEQuery
{
    public partial class SQLDesigner : UserControl
    {
        int x;
        int y;
        int nextStartX, nextStartY;
        Bitmap bmpBack = null;
        public List<CETable> CETables { get; set; }
        public List<CETableRelation> CETableRelations { get; set; }
        private List<Line> lines;
        private List<TablePanel> tables;
        private List<Relation> relations;
        private List<QueryColumn> queryColumns;
        private struct Line
        {
            public TablePanel mark1;
            public TablePanel mark2;
            public int Width;
        }

        public event DesignerColseEventHandler DesignerColse;
        protected virtual void OnDesignerColse(DesignerColseEventArgs e)
        {
            DesignerColse(this, e);
        }
        public SQLDesigner()
        {
            InitializeComponent();
            lines = new List<Line>();
            tables = new List<TablePanel>();
            relations = new List<Relation>();
            queryColumns = new List<QueryColumn>();
            nextStartX = 0;
            nextStartY = 0;

            bmpBack = new Bitmap(image.Width, image.Height);
            Graphics.FromImage(bmpBack).Clear(Color.White);
            image.Image = (Bitmap)bmpBack.Clone();
           
            this.SetStyle(ControlStyles.DoubleBuffer, true);
            this.SetStyle(ControlStyles.UserPaint, true);
            this.SetStyle(ControlStyles.AllPaintingInWmPaint, true);

            
            
        }
        
        private void SQLDesigner_Load(object sender, EventArgs e)
        {
            try
            {
                image.innerPicture.Controls.Clear();
                dgRelations.AutoGenerateColumns = false;
                //Future Implementation
                dgColumns.Columns[4].Visible = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        void pnlMenu_Click(object sender, EventArgs e)
        {
            try
            {
                foreach (TablePanel pnl in tables)
                {
                    if (pnl.TableName == ((ToolStripItem)sender).Text)
                    {
                        image.innerPicture.Controls.Add(pnl);
                        pnl.IsTableSelected = false;
                        //pnl.Location = new Point(0, 0);
                        pnl.Location = new Point(nextStartX, nextStartY);
                        nextStartX += 25;
                        nextStartY += 25;
                        pnl.ClearSelection();
                        pnl.ColumnCheck += new ColumnCheckEventHandler(pnl_ColumnCheck);
                    }
                }
                Redraw();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        void pnl_ColumnCheck(object sender, ColumnCheckEventArgs e)
        {
            try
            {
                QueryColumn queryColumn = new QueryColumn();
                queryColumn.TableName = e.TableName;
                queryColumn.ColumnName = e.ColumnName;
                queryColumn.IsOutput = true;
                queryColumn.SortType = "None";
                if (e.IsColumnChecked)
                {
                    if (!queryColumns.Exists(delegate(QueryColumn column) { return queryColumn.TableName == column.TableName && queryColumn.ColumnName == column.ColumnName; }))
                    {
                        queryColumns.Add(queryColumn);
                    }
                }
                else
                {
                    if (queryColumns.Exists(delegate(QueryColumn column) { return queryColumn.TableName == column.TableName && queryColumn.ColumnName == column.ColumnName; }))
                    {
                        queryColumns.RemoveAt(queryColumns.FindIndex(delegate(QueryColumn column) { return queryColumn.TableName == column.TableName && queryColumn.ColumnName == column.ColumnName; }));
                    }
                }
                dgColumns.DataSource = null;
                dgColumns.DataSource = queryColumns;
                //Future Implementation
                dgColumns.Columns[4].Visible = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        void panel_MouseUp(object sender, MouseEventArgs e)
        {
            try
            {
                if (e.Button == MouseButtons.Left)
                {
                    ResumeLayout();
                    Redraw();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        void panel_MouseMove(object sender, MouseEventArgs e)
        {
            try
            {
                if (e.Button == MouseButtons.Left)
                {
                    Point p = new Point(e.X - x + ((TablePanel)sender).Left, e.Y - y + ((TablePanel)sender).Top);
                    ((TablePanel)sender).Location = p;

                    //Line l = getLineByMark((TablePanel)sender);
                    List<Line> a = getLinesByMark((TablePanel)sender);
                    Redraw(a, p);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        void panel_MouseDown(object sender, MouseEventArgs e)
        {
            try
            {
                if (e.Button == MouseButtons.Left)
                {
                    this.SuspendLayout();
                    x = e.X;
                    y = e.Y;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void deleteTableToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                foreach (TablePanel pnl in image.innerPicture.Controls)
                {
                    if (pnl.IsTableSelected)
                    {
                        image.innerPicture.Controls.Remove(pnl);
                        foreach (Line line in getLinesByMark(pnl))
                        {
                            lines.Remove(line);
                        }
                        foreach (Relation rel in getRelationByMark(pnl))
                        {
                            relations.Remove(rel);
                        }
                        foreach (QueryColumn item in queryColumns.FindAll(delegate(QueryColumn column) { return column.TableName == pnl.TableName; }))
                        {
                            queryColumns.RemoveAt(queryColumns.FindIndex(delegate(QueryColumn column) { return item.TableName == column.TableName && item.ColumnName == column.ColumnName; }));
                        }
                        dgColumns.DataSource = null;
                        dgColumns.DataSource = queryColumns;
                        //Future Implementation
                        dgColumns.Columns[4].Visible = false;

                        dgRelations.DataSource = null;
                        dgRelations.DataSource = relations;
                        Redraw();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }
        private void addRelationToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                PopRelation rel = new PopRelation();
                rel.Tables = CETables;
                Popup popup = new Popup(rel);
                popup.Resizable = false;
                popup.ShowingAnimation = PopupAnimations.Blend;
                popup.SetBounds(image.Left, image.Top, image.Width, image.Height);
                popup.Closed += new ToolStripDropDownClosedEventHandler(popup_Closed);
                popup.Show(pnlDrawPane);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        void popup_Closed(object sender, ToolStripDropDownClosedEventArgs e)
        {
            try
            {
                //MessageBox.Show(((Popup)sender).Tag.ToString());
                if (((Popup)sender).Tag != null)
                {
                    CETableRelation relation = ((Popup)sender).Tag as CETableRelation;
                    foreach (Relation relCheck in relations)
                    {
                        if ((relCheck.LeftTableName == relation.LeftTableName || relCheck.RightTableName == relation.LeftTableName) &&
                            (relCheck.LeftTableName == relation.RightTableName || relCheck.RightTableName == relation.RightTableName))
                        {
                            return;
                        }
                    }
                    Relation rel = new Relation();
                    if (!image.innerPicture.Controls.Contains(getTablePanelByname(relation.LeftTableName)))
                    {
                        foreach (TablePanel pnl in tables)
                        {
                            if (pnl.TableName == relation.LeftTableName)
                            {
                                image.innerPicture.Controls.Add(pnl);
                                pnl.IsTableSelected = false;
                                //pnl.Location = new Point(0, 0);
                                pnl.Location = new Point(nextStartX, nextStartY);
                                nextStartX += 25;
                                nextStartY += 25;
                                pnl.ClearSelection();
                                pnl.ColumnCheck += new ColumnCheckEventHandler(pnl_ColumnCheck);
                            }
                        }
                    }
                    if (!image.innerPicture.Controls.Contains(getTablePanelByname(relation.RightTableName)))
                    {
                        foreach (TablePanel pnl in tables)
                        {
                            if (pnl.TableName == relation.RightTableName)
                            {
                                image.innerPicture.Controls.Add(pnl);
                                pnl.IsTableSelected = false;
                                //pnl.Location = new Point(0, 0);
                                pnl.Location = new Point(nextStartX, nextStartY);
                                nextStartX += 25;
                                nextStartY += 25;
                                pnl.ClearSelection();
                                pnl.ColumnCheck += new ColumnCheckEventHandler(pnl_ColumnCheck);
                            }
                        }
                    }
                    rel.Left = getTablePanelByname(relation.LeftTableName);
                    rel.Right = getTablePanelByname(relation.RightTableName);
                    rel.LeftColumn = string.Join(",", relation.LeftColumns.ToArray());
                    rel.RightColumn = string.Join(",", relation.RightColumns.ToArray());
                    rel.JoinType = relation.JoinTypeName;
                    rel.CERelation = relation;
                    if (!relations.Contains(rel))
                    {
                        relations.Add(rel);

                        Line line = new Line();
                        line.mark1 = rel.Left;
                        line.mark2 = rel.Right;
                        line.Width = 2;
                        if (!lines.Contains(line))
                        {
                            lines.Add(line);
                        }
                    }

                    dgRelations.DataSource = null;
                    dgRelations.DataSource = relations;
                    //foreach (TablePanel pnlClear in image.innerPicture.Controls)
                    //{
                    //    pnlClear.ClearSelection();
                    //}
                    Redraw();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void generateQueryToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                StringBuilder sbSelect = new StringBuilder();
                StringBuilder sbFrom = new StringBuilder();
                StringBuilder sbWhere = new StringBuilder();
                StringBuilder sbOrderBy = new StringBuilder();
                string strFormatted = string.Empty;
                string strQuery = string.Empty;


                sbSelect.Append("SELECT" + Environment.NewLine);
                sbWhere.Append("WHERE" + Environment.NewLine);
                sbOrderBy.Append("ORDER BY" + Environment.NewLine);
                foreach (DataGridViewRow row in dgColumns.Rows)
                {
                    if (bool.Parse(row.Cells[2].Value.ToString()))
                    {
                        sbSelect.Append(string.Format("\t [{0}].[{1}], {2}", row.Cells[0].Value.ToString(), row.Cells[1].Value.ToString(), Environment.NewLine));
                    }
                    if (!string.IsNullOrEmpty(row.Cells[5].Value.ToString()))
                    {
                        sbWhere.Append(string.Format("\t [{0}].[{1}] {2} AND {3}", row.Cells[0].Value.ToString(), row.Cells[1].Value.ToString(), row.Cells[5].Value.ToString(), Environment.NewLine));
                    }
                    if (row.Cells[3].Value.ToString().ToUpper() != "NONE")
                    {
                        sbOrderBy.Append(string.Format("\t [{0}].[{1}] {2}, {3}", row.Cells[0].Value.ToString(), row.Cells[1].Value.ToString(), row.Cells[3].Value.ToString(), Environment.NewLine));
                    }
                }
                if (sbSelect.ToString().Length > 8)
                {
                    strFormatted = sbSelect.ToString().Substring(0, sbSelect.ToString().Length - 4);
                    sbSelect = new StringBuilder();
                    sbSelect.Append(string.Format("{0} {1}", strFormatted, Environment.NewLine));
                }
                if (sbWhere.ToString().Length > 7)
                {
                    strFormatted = sbWhere.ToString().Substring(0, sbWhere.ToString().Length - 6);
                    sbWhere = new StringBuilder();
                    sbWhere.Append(string.Format("{0} {1}", strFormatted, Environment.NewLine));
                }
                if (sbOrderBy.ToString().Length > 10)
                {
                    strFormatted = sbOrderBy.ToString().Substring(0, sbOrderBy.ToString().Length - 4);
                    sbOrderBy = new StringBuilder();
                    sbOrderBy.Append(string.Format("{0} {1}", strFormatted, Environment.NewLine));
                }
                if (relations.Count == 0)
                {
                    foreach (string nojointab in (dgColumns.DataSource as List<QueryColumn>).Select(q => q.TableName).Distinct())
                    {
                        sbFrom.Append("[" + nojointab + "] CROSS JOIN ");
                    }
                    string strCross = sbFrom.ToString().Substring(0, sbFrom.ToString().Length - 12);
                    sbFrom = new StringBuilder();
                    sbFrom.Append(string.Format("FROM {0} {1}", strCross, Environment.NewLine));
                }
                else
                {
                    int index = 0;
                    sbFrom.Append("FROM" + Environment.NewLine);
                    foreach (Relation rel in relations)
                    {
                        if (sbFrom.ToString().IndexOf(rel.LeftTableName) > -1)
                        {
                            sbFrom.Append("\t" + rel.SQLRelationWOLeft + Environment.NewLine);
                        }
                        else
                        {
                            sbFrom.Append("\t" + (index > 0 ? "CROSS JOIN " : string.Empty) + rel.SQLRelationWhole + Environment.NewLine);
                        }
                    }
                    foreach (string nojointab in (dgColumns.DataSource as List<QueryColumn>).Select(q => q.TableName).Distinct())
                    {
                        bool isExistInFrom = false;
                        foreach (Relation rel in relations)
                        {
                            if (rel.LeftTableName == nojointab || rel.RightTableName == nojointab)
                            {
                                isExistInFrom = true;
                            }
                        }
                        if (!isExistInFrom)
                        {
                            sbFrom.Append("\t CROSS JOIN" + nojointab + Environment.NewLine);
                        }

                    }
                }

                strQuery = sbSelect.ToString() + sbFrom.ToString();
                if (sbWhere.ToString().Length > 7)
                {
                    strQuery += sbWhere.ToString();
                }
                if (sbOrderBy.ToString().Length > 10)
                {
                    strQuery += sbOrderBy.ToString();
                }
                txtQuery.Text = strQuery;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void dgColumns_RowsAdded(object sender, DataGridViewRowsAddedEventArgs e)
        {
            try
            {
                DataGridViewComboBoxCell cc = new DataGridViewComboBoxCell();
                cc.Items.Add("None");
                cc.Items.Add("ASC");
                cc.Items.Add("DESC");
                dgColumns[3, e.RowIndex] = cc;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void closeToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                DesignerColseEventArgs designerCloseArgs = new DesignerColseEventArgs();
                designerCloseArgs.Query = this.txtQuery.Text.Trim();
                OnDesignerColse(designerCloseArgs);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public void LoadDesigner()
        {
            try
            {
                if (CETables != null)
                {
                    TablePanel tablePanel;
                    foreach (CETable tbl in CETables)
                    {
                        tablePanel = new TablePanel();
                        tablePanel.TableName = tbl.TableName;
                        tablePanel.Columns = new List<string>();
                        foreach (string column in tbl.Columns)
                        {
                            tablePanel.Columns.Add(column);
                        }
                        tablePanel.MouseDown += new MouseEventHandler(panel_MouseDown);
                        tablePanel.MouseUp += new MouseEventHandler(panel_MouseUp);
                        tablePanel.MouseMove += new MouseEventHandler(panel_MouseMove);
                        tables.Add(tablePanel);
                    }
                    foreach (TablePanel pnl in tables)
                    {
                        addTableToolStripMenuItem.DropDown.Items.Add(pnl.TableName, null, pnlMenu_Click);
                    }
                    Redraw();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private void DrawLine(Line line)
        {
            Graphics g = null;

            g = Graphics.FromImage(image.Image);
            g.DrawLine(new Pen(Color.Black, (float)line.Width), line.mark1.Center.X, line.mark1.Center.Y, line.mark2.Center.X, line.mark2.Center.Y);
            g.Dispose();
        }
        private void Redraw(List<Line> lines, Point p)
        {
            Graphics.FromImage(image.Image).DrawImage(bmpBack, 0, 0, getMaxSize().Width, getMaxSize().Height); //image.Image.Width,image.Image.Height);

            foreach (Line l in lines)
            {
                DrawLine(l);

            }
            foreach (Line l in lines)
            {
                Region r = getRegionByLine(l, p);
                image.innerPicture.Invalidate(r);
                image.innerPicture.Update();
            }
        }        
        private void Redraw()
        {
            if (bmpBack != null)
            {
                image.Image = null;
                bmpBack = new Bitmap(getMaxSize().Width, getMaxSize().Height);
                Graphics.FromImage(bmpBack).Clear(Color.White);
                image.Image = (Bitmap)bmpBack.Clone();
            }
            else
            {
                image.Image = new Bitmap(image.Width, image.Height);
                Graphics.FromImage(image.Image).Clear(Color.Transparent);
            }
            foreach (Line line in lines)
            {
                DrawLine(line);
            }
            image.innerPicture.Refresh();
        }        

        private TablePanel getTablePanelByname(string m)
        {
            foreach (TablePanel l in tables)
            {
                if (l.TableName == m)
                    return l;
                    
            }//Never happens :D
            return null;
            //throw new Exception("No line found");
        }
        private List<Line> getLinesByMark(TablePanel m)
        {
            List<Line> markLines = new List<Line>();
            foreach (Line l in lines)
            {
                if (l.mark1 == m || l.mark2 == m)
                    //return l;
                    markLines.Add(l);
            }//Never happens :D
            return markLines;
            //throw new Exception("No line found");
        }
        private List<Relation> getRelationByMark(TablePanel m)
        {
            List<Relation> markRelations = new List<Relation>();
            foreach (Relation l in relations)
            {
                if (l.Left == m || l.Right == m)
                    //return l;
                    markRelations.Add(l);
            }//Never happens :D
            return markRelations;
            //throw new Exception("No line found");
        }
        private Region getRegionByLine(Line l, Point p)
        {
            GraphicsPath gp = new GraphicsPath();
            gp.AddPolygon(new Point[] { l.mark1.Center, l.mark2.Center, p, l.mark1.Center });

            RectangleF rf = gp.GetBounds();
            gp.Dispose();

            rf.Inflate(100f, 100f);

            return new Region(rf);
        }
        private Size getMaxSize()
        {
            int maxWidth, maxHeight;
            maxHeight = image.Height;
            maxWidth = image.Width;

            foreach (TablePanel pnl in tables)
            {
                if (pnl.Top + pnl.Height > maxHeight)
                    maxHeight = pnl.Top + pnl.Height + 20;
                if (pnl.Left + pnl.Width > maxWidth)
                    maxWidth = pnl.Left + pnl.Width + 20;
            }
            return new Size(maxWidth, maxHeight);
        }
    }
    internal class Relation
    {
        private TablePanel left;
        private TablePanel right;        
        public TablePanel Left { get { return this.left; } set { this.left = value; LeftTableName = value.TableName; } }
        public TablePanel Right { get { return this.right; } set { this.right = value; RightTableName = value.TableName; } }

        public string LeftTableName { get; private set; }
        public string RightTableName { get; private set; }
        public string LeftColumn { get; set; }
        public string RightColumn { get; set; }
        public string JoinType { get; set; }
        public CETableRelation CERelation { get; set; }
        public string SQLRelationWhole
        {
            get
            {
                string sql = string.Format("[{0}] {1} JOIN [{2}] ON ", CERelation.LeftTableName, CERelation.JoinTypeName, CERelation.RightTableName);
                string sqlAnd = string.Empty;
                for (int index = 0; index < CERelation.LeftColumns.Count; index++)
                {
                    sqlAnd += string.Format("[{0}].[{1}] = [{2}].[{3}] AND",CERelation.LeftTableName, CERelation.LeftColumns[index],CERelation.RightTableName,CERelation.RightColumns[index]);
                }
                if (sqlAnd.Length > 0)
                {
                    sqlAnd = sqlAnd.Substring(0, sqlAnd.Length - 4);
                }
                return sql + sqlAnd;
            }
        }
        public string SQLRelationWOLeft
        {
            get
            {
                string sql = string.Format("{0} JOIN [{1}] ON ", CERelation.JoinTypeName, CERelation.RightTableName);
                string sqlAnd = string.Empty;
                for (int index = 0; index < CERelation.LeftColumns.Count; index++)
                {
                    sqlAnd += string.Format("[{0}].[{1}] = [{2}].[{3}] AND", CERelation.LeftTableName, CERelation.LeftColumns[index], CERelation.RightTableName, CERelation.RightColumns[index]);
                }
                if (sqlAnd.Length > 0)
                {
                    sqlAnd = sqlAnd.Substring(0, sqlAnd.Length - 4);
                }
                return sql + sqlAnd;
            }
        }
    }
    internal class QueryColumn
    {
        public string TableName {get; set; }
        public string ColumnName { get; set; }
        public bool IsOutput { get; set; }
        public string SortType { get; set; }
        public int? SortOrder { get; set; }
        public string Filter { get; set; }
        public QueryColumn()
        {
            TableName = string.Empty;
            ColumnName = string.Empty;
            SortType = string.Empty;
            SortOrder = null;
            Filter = string.Empty;
        }
    }
    [Serializable]
    public class CETable
    {
        public string TableName { get; set; }
        public List<string> Columns { get; set; }

        public CETable()
        {
            TableName = string.Empty;
            Columns = new List<string>();
        }
    }
    [Serializable]
    public class CETableRelation
    {
        public string LeftTableName { get; set; }
        public string RightTableName { get; set; }
        public string JoinTypeName { get; set; }
        public List<string> LeftColumns { get; set; }
        public List<string> RightColumns { get; set; }
    }
    public delegate void DesignerColseEventHandler(object sender, DesignerColseEventArgs e);
    public class DesignerColseEventArgs : EventArgs
    {
        public string Query { get; set; }        
    }
}
